#!/usr/bin/env python
# -*- coding:utf-8 -*-

from excel.EGC.EgcDictUtil import EgcDictUtil as DictUtil
from excel.EGC.EgcObj import EgcObj
from excel.PruTuutDb import PruTuutDb

pru_tuut_db_conn = PruTuutDb.get_pool()
dict_db = DictUtil(pru_tuut_db_conn)


# **
# 参考计算EXCEL原文件中列表左侧的数据列
# 释义 =======> 对应列 =======> 公式
# 翌年歲
# 每年儲蓄金額
# 儲蓄供款期
# 重複儲蓄計劃
# 重複儲蓄增長率
# 貨幣
# 退保翌年歲
# 提款開始翌年歲
# 首年提款
# 通脹率
# 提取年期
# 推廣優惠
# 一筆過預繳
class EgcBaseTabTop:

    def __init__(self):
        return

    EgcBaseTabTop = {
        "lumsum": 0,
        "lumpsum_discount": 0,
        "Saving_Sum_Assured": 0,
        "SA": 0,
        "1Y_discount": 0,
        "3Y_discount": 0,
        "5Y_discount": 0,
        "8Y_discount": 0,
        "12Y_discount": 0,
        "EGC_discount": 0,
        "discount_adjusted_premium": 0,
        "discount_adjusted_SA": 0,
        "Chi": 0,
        "Exchange_rate": 0,
        "Display_rate": 0,
        "Underlying_currency": 0,
        "Display_currenty": 0,
        "EGC_payment_term_index": 0,
        "EGC_currency_index": 0,
        "EGC_sum_assured_index": 0,
        "EGC_SA_factor": 0,
        "EGC_annual_premium": 0,
        "EGC_annual_premium_(HKD)": 0,
        "Adjusted_EGC_CV_col": 0,
        "Total_EGC_premium": 0,
        "SA_minimum": 0,
        "low_SA_message": 0,
        "max_withdrawal_age": 0,
        "surrender_value": 0,
        "seed_value": 0,
        "max_withdrawal": 0,
    }

    # 计算 lumsum
    # Params:
    # 一筆過預繳 F11 : lump_sum_prepaid
    # 貨幣 C10 currency
    # 儲蓄供款期 C6 Saving Payment Term
    def get_lumsum(self, lump_sum_prepaid, saving_payment_term, currency):
        # 参考计算公式
        # =AND(LOWER(F11)="y",C6=5,OR(LOWER(C10)="hkd",LOWER(C10)="usd",LOWER(C10)="hkd-u",LOWER(C10)="rmb-u"))
        blr_currency = str(currency).lower() == "hkd" or \
                       str(currency).lower() == "usd" or \
                       str(currency).lower() == "hkd_u" or \
                       str(currency).lower() == "rmb_u"
        blr_saving_payment_term = saving_payment_term == 5
        return str(lump_sum_prepaid).lower() == "y" and blr_saving_payment_term and blr_currency

    # 计算 lumpsum discount
    # Params:
    # lumsum C18
    # 貨幣 currency C10
    # EGC annual premium C40
    def get_lumpsum_discount(self, lumsum, currency, egc_annual_premium):
        # 参考计算公式
        # =IF(C18,IF(LOWER(C10)="hkd",35.293%,37.01%)*C40,0)
        if not lumsum:
            return 0
        else:
            # IF(LOWER(C10) = "hkd", 35.293 %, 37.01 %)*C40
            if str(currency).lower() == "hkd":
                cal_val = 35.293 / 100
            else:
                cal_val = 37.01 / 100
            return cal_val * egc_annual_premium

    # 计算 lumpsum discount
    # Params:
    # discount adjusted SA : C29
    def get_Saving_Sum_Assured(self, cal_discount_adjusted_SA):
        # 参考计算公式
        # =C29
        return cal_discount_adjusted_SA

    # 计算 SA
    # Params:
    # SA B21 : SA
    # 每年儲蓄金額 Annual Saving :C5
    # Display rate :C32
    def get_SA(self, saving_payment_term, annual_saving, display_rate):
        # 参考计算公式
        # =VLOOKUP("SA",data4!A2:FL102,C37,FALSE)*(C5/C32/VLOOKUP("ANB",data4!A2:FL102,C37,FALSE))
        # idx_tab = EgcObj.SAVING_PAYMENT_TERM[saving_payment_term]
        idx_tab = EgcObj.EGC_SUM_ASSURED_INDEX[saving_payment_term]
        cal_sa = EgcObj.SA_VALUE[idx_tab]
        cal_anb = EgcObj.ANB_VALUE[idx_tab]
        return round(cal_sa * (annual_saving / display_rate / cal_anb), 5)

    # 计算 1Y discount
    # Params:
    # SA C21
    # Exchange rate C31
    def get_1Y_discount(self, sa, exchange_rate):
        # 参考计算公式
        # =IF(C21/C32>=150000,30.2,IF(C21/C32>=100000,23.6,IF(C21/C32>=50000,19.8,IF(C21/C32>=35000,17,IF(C21/C32>=20000,11.4,0)))))-30.2
        cal_val = 0
        if sa / exchange_rate >= 150000:
            cal_val = 3
        elif sa / exchange_rate >= 100000:
            cal_val = 2.4
        elif sa / exchange_rate >= 50000:
            cal_val = 2
        elif sa / exchange_rate >= 35000:
            cal_val = 1.6
        elif sa / exchange_rate >= 20000:
            cal_val = 1
        return cal_val - 30.2

    # 计算 3Y discount
    # Params:
    # SA C21
    # Exchange rate C31
    def get_3Y_discount(self, sa, exchange_rate):
        # 参考计算公式
        # =IF(C21/C31>=150000,10.4,IF(C21/C31>=100000,8,IF(C21/C31>=50000,6.8,IF(C21/C31>=35000,5.8,IF(C21/C31>=20000,3.8,0)))))-10.4
        cal_val = 0
        if sa / exchange_rate >= 150000:
            cal_val = 3
        elif sa / exchange_rate >= 100000:
            cal_val = 2.4
        elif sa / exchange_rate >= 50000:
            cal_val = 2
        elif sa / exchange_rate >= 35000:
            cal_val = 1.6
        elif sa / exchange_rate >= 20000:
            cal_val = 1
        return cal_val - 10.4

    # 计算 5Y discount
    # Params:
    # SA C21
    # Exchange rate C31
    def get_5Y_discount(self, sa, exchange_rate):
        # 参考计算公式
        # =IF(C21/C31>=150000,6.4,IF(C21/C31>=100000,5,IF(C21/C31>=50000,4.2,IF(C21/C31>=35000,3.6,IF(C21/C31>=20000,2.4,0)))))-6.4
        cal_val = 0
        if sa / exchange_rate >= 150000:
            cal_val = 3
        elif sa / exchange_rate >= 100000:
            cal_val = 2.4
        elif sa / exchange_rate >= 50000:
            cal_val = 2
        elif sa / exchange_rate >= 35000:
            cal_val = 1.6
        elif sa / exchange_rate >= 20000:
            cal_val = 1
        return cal_val - 6.4

    # 计算 8Y discount
    # Params:
    # SA C21
    # Exchange rate C31
    def get_8Y_discount(self, sa, exchange_rate):
        # 参考计算公式
        # =IF(C21/C31>=150000,4,IF(C21/C31>=100000,3.2,IF(C21/C31>=50000,2.6,IF(C21/C31>=35000,2.2,IF(C21/C31>=20000,1.6,0)))))-4
        cal_val = 0
        if sa / exchange_rate >= 150000:
            cal_val = 3
        elif sa / exchange_rate >= 100000:
            cal_val = 2.4
        elif sa / exchange_rate >= 50000:
            cal_val = 2
        elif sa / exchange_rate >= 35000:
            cal_val = 1.6
        elif sa / exchange_rate >= 20000:
            cal_val = 1
        return cal_val - 4

    # 计算 12Y discount
    # Params:
    # SA C21
    # Exchange rate C31
    def get_12Y_discount(self, sa, exchange_rate):
        # 参考计算公式
        # =IF(C21/C31>=150000,3,IF(C21/C31>=100000,2.4,IF(C21/C31>=50000,2,IF(C21/C31>=35000,1.6,IF(C21/C31>=20000,1,0)))))-3
        cal_val = 0
        if sa / exchange_rate >= 150000:
            cal_val = 3
        elif sa / exchange_rate >= 100000:
            cal_val = 2.4
        elif sa / exchange_rate >= 50000:
            cal_val = 2
        elif sa / exchange_rate >= 35000:
            cal_val = 1.6
        elif sa / exchange_rate >= 20000:
            cal_val = 1
        return cal_val - 3

    # 计算 EGC discount
    # Params:
    def get_EGC_discount(self, ):
        # 参考计算公式
        return 0

    # 计算 discount adjusted premium
    # Params:
    # Saving Payment Term C6
    # SA C21
    # EGC discount C27
    def get_discount_adjusted_premium(self, saving_payment_term, egc_discount, sa):
        # 参考计算公式
        # =(VLOOKUP("ANB",data4!A2:FL102,C38,FALSE)*1000/VLOOKUP("SA",data4!A2:FL102,C38,FALSE)-C27)*C21/1000
        # idx_tab = EgcObj.SAVING_PAYMENT_TERM[saving_payment_term]
        idx_tab = EgcObj.EGC_SUM_ASSURED_INDEX[saving_payment_term]
        cal_sa = EgcObj.SA_VALUE[idx_tab]
        cal_anb = EgcObj.ANB_VALUE[idx_tab]
        return (cal_anb * 1000 / cal_sa - egc_discount) * sa / 1000

    # 计算 discount adjusted SA
    # Params:
    # annual_saving C5
    # sa C21
    # discount_adjusted_premium C28
    # display_rate C32
    def get_discount_adjusted_SA(self, annual_saving, sa, discount_adjusted_premium, display_rate):
        # 参考计算公式
        # =ROUNDUP(C5/(C28*C33)*C21/100,0)*100
        _res = round(round(annual_saving / (discount_adjusted_premium * display_rate) * sa, 5) / 100, 6)
        return roundup(_res) * 100

    # 计算 Chi
    # Params:
    def get_Chi(self, B160):
        # 参考计算公式
        # =IF(LOWER(B160)="chi","Yes","No")
        return "Yes" if B160 == "chi" else "No"

    # 计算 Exchange rate
    # Params:
    # 貨幣 currency C10
    def get_Exchange_rate(self, currency):
        # 参考计算公式
        # =IF(LOWER(C10)="hkd",8,1)
        return 8 if str(currency).lower() == "hkd" else 1

    # 计算 Display rate
    # Params:
    # 貨幣 currency C10
    def get_Display_rate(self, currency):
        # 参考计算公式
        # =IF(LOWER(C10)="rmb-u",Notes!B2,IF(LOWER(C10)="hkd-u",7.8,1))
        if str(currency).lower() == "rmb_u":
            return EgcObj.USD_2_RMB_RATE
        elif str(currency).lower() == "hkd_u":
            return EgcObj.HKD_2_RMB_RATE
        return EgcObj.USD_RATE

    # 计算 Underlying currency
    # Params:
    # 貨幣 currency C10
    def get_Underlying_currency(self, currency):
        # 参考计算公式
        # =IF(LOWER(C10)="hkd","(HKD)",IF(LOWER(C10)="rmb","(RMB)",IF(LOWER(C10)="aud","(AUD)",IF(LOWER(C10)="cad","(CAD)",IF(LOWER(C10)="gbp","(GBP)","(USD)")))))
        if str(currency).lower() == "rmb":
            return "(USD)"
        elif str(currency).lower() == "hkd":
            return "(HKD)"
        elif str(currency).lower() == "usd":
            return "(USD)"

    # 计算 Display currenty
    # Params:
    # 貨幣 currency C10
    def get_Display_currenty(self, currency):
        # 参考计算公式
        # =IF(OR(LOWER(C10)="rmb-u",LOWER(C10)="rmb"),"(RMB)",IF(OR(LOWER(C10)="hkd-u",LOWER(C10)="hkd"),"(HKD)",IF(LOWER(C10)="aud","(AUD)",IF(LOWER(C10)="cad","(CAD)",IF(LOWER(C10)="gbp","(GBP)","(USD)")))))
        return EgcObj.switch_C10_dict(currency)

    # 计算 EGC payment term index
    # Params:
    # 儲蓄供款期 currency C6
    def get_EGC_payment_term_index(self, saving_payment_term):
        # 参考计算公式
        # =IF(C6=3, 145, 151)
        return 145 if saving_payment_term == 3 else 151

    # 计算 EGC currency index
    # Params:
    # 貨幣 currency C10
    # EGC currency index C36
    def get_EGC_currency_index(self, currency, egc_currency_index):
        # 参考计算公式
        # =IF(LOWER(C10)="hkd",C36+0,
        # IF(LOWER(C10)="rmb",C36+2,
        # IF(LOWER(C10)="aud",C36+3,
        # IF(LOWER(C10)="cad",C36+4,
        # IF(LOWER(C10)="gbp",C36+5,C36+1)))))
        if str(currency).lower() == "hkd":
            return egc_currency_index + 0
        elif str(currency).lower() == "rmb":
            return egc_currency_index + 2
        elif str(currency).lower() == "aud":
            return egc_currency_index + 3
        elif str(currency).lower() == "cad":
            return egc_currency_index + 4
        elif str(currency).lower() == "gbp":
            return egc_currency_index + 5
        else:
            return egc_currency_index + 1

    # 计算 EGC sum assured index
    # Params:
    # EGC sum assured index C37
    def get_EGC_sum_assured_index(self, currency, egc_currency_index):
        # 参考计算公式
        # =C36
        return self.get_EGC_currency_index(currency, egc_currency_index)

    # 计算 EGC SA factor
    # Params:
    # Saving Sum Assured C20
    def get_EGC_SA_factor(self, saving_sum_assured, saving_payment_term):
        # 参考计算公式
        # =C20/VLOOKUP("SA",data4!A2:FL102,C37,FALSE)
        # C20 = Saving Sum Assured
        # EGC sum assured index C37
        # idx_tab = EgcObj.SAVING_PAYMENT_TERM[saving_payment_term]
        idx_tab = EgcObj.EGC_SUM_ASSURED_INDEX[saving_payment_term]
        cal_val = EgcObj.SA_VALUE[idx_tab]
        return saving_sum_assured / cal_val

    # 计算 EGC annual premium
    # Params:
    # Saving Payment Term C6
    # Saving Sum Assured C20
    # EGC discount C27
    def get_EGC_annual_premium(self, saving_payment_term, saving_sum_assured, egc_discount):
        # 参考计算公式
        # =(VLOOKUP("ANB",data4!A2:FL102,C37,FALSE)*1000/VLOOKUP("SA",data4!A2:FL102,C37,FALSE)-C27)*C20/1000
        # idx_tab = EgcObj.SAVING_PAYMENT_TERM[saving_payment_term]
        idx_tab = EgcObj.EGC_SUM_ASSURED_INDEX[saving_payment_term]
        cal_sa = EgcObj.SA_VALUE[idx_tab]
        cal_anb = EgcObj.ANB_VALUE[idx_tab]
        return (cal_anb * 1000 / cal_sa - egc_discount) * saving_sum_assured / 1000

    # 计算 EGC annual premium HKD
    # Params:
    # 貨幣 currency C10
    def get_EGC_annual_premium_HKD(self, currency, egc_annual_premium):
        # 参考计算公式
        # =IF(LOWER(C10)="hkd",1,
        # IF(LOWER(C10)="rmb",1.2,
        # IF(LOWER(C10)="aud",6,
        # IF(LOWER(C10)="cad",6.5,
        # IF(LOWER(C10)="gbp",11,7.8)))))*C39
        cal_val = 0
        if str(currency).lower() == "hkd":
            return cal_val + 1
        elif str(currency).lower() == "rmb":
            return cal_val + 1.2
        elif str(currency).lower() == "aud":
            return cal_val + 6
        elif str(currency).lower() == "cad":
            return cal_val + 6.5
        elif str(currency).lower() == "gbp":
            return cal_val + 11
        else:
            return cal_val + 7.8
        return cal_val * egc_annual_premium

    # 计算 Adjusted EGC CV col
    # Params:
    def get_Adjusted_EGC_CV_col(self, nums):
        # 参考计算公式
        # =COLUMN(AC56)-COLUMN(B56)+1
        return 28

    # 计算 Total EGC premium
    # Params:
    def get_Total_EGC_premium(self, nums):
        # 参考计算公式
        # =SUM(V57:V155)
        return sum(nums)

    # 计算 SA minimum
    # Params:
    def get_SA_minimum(self, currency):
        # 参考计算公式
        # =IF(LOWER(C10)="hkd",80000,IF(LOWER(C10)="rmb",60000,IF(LOWER(C10)="aud",12000,IF(LOWER(C10)="cad",12000,IF(LOWER(C10)="gbp",6000,10000)))))
        if str(currency).lower() == "hkd":
            return 80000
        elif str(currency).lower() == "rmb":
            return 60000
        elif str(currency).lower() == "aud":
            return 12000
        elif str(currency).lower() == "cad":
            return 12000
        elif str(currency).lower() == "gbp":
            return 6000
        else:
            return 10000

    # 计算 low SA message
    # Params:
    def get_low_SA_message(self, ):
        # 参考计算公式
        # =IF(C31="Yes","名義金額過低","Notional Amount Too Low")
        return 0

    # 计算 max withdrawal age
    # Params:
    # 退保翌年歲 F3 : Surrender ANB
    # 提款開始翌年歲 F5 : Withdrawal Starting ANB
    # 提取年期 F8 : Withdrawal Years
    def get_max_withdrawal_age(self, surrender_anb, withdrawal_starting_anb, withdrawal_years):
        # 参考计算公式
        # =IF(F8=0,F3,F5+F8-1)
        return surrender_anb if withdrawal_years == 0 else withdrawal_starting_anb + withdrawal_years - 1

    # 计算 surrender value
    # Params:
    def get_surrender_value(self, max_withdrawal_age, rows):
        # 参考计算公式
        # =VLOOKUP(C48,B57:AI157,COLUMN(AI157)-COLUMN(B57)+1,FALSE)
        # C48 = max withdrawal age
        # COLUMN(AI157)-COLUMN(B57)+1 = Total A. EGC CV
        return rows[91]

    # 计算 seed value
    # Params:
    # 翌年歲 F3 : Surrender_ANB
    # 提款開始翌年歲 F5 : Withdrawal_Starting_ANB
    # 提取年期 F8 : Withdrawal Years
    # 提取年期 C46 : surrender value
    def get_seed_value(self, surrender_value, surrender_anb, withdrawal_starting_anb, withdrawal_years, ):
        # 参考计算公式
        # =C46/(IF(F8=0,F3-F5+1,F8))
        cal_val = (surrender_anb - withdrawal_starting_anb + 1) if withdrawal_years == 0 else withdrawal_years
        return surrender_value / cal_val

    # 计算 max withdrawal
    # Params:
    # DK46 : ANB
    # display_rate C32 : Display rate
    def get_max_withdrawal(self, display_rate):
        # 参考计算公式
        # =ROUNDDOWN(DK46*C32/1000,0)*1000
        return 0


def roundup(val):
    if isinstance(val, float):
        return int(val + 1)
    return val
